package com.asyf.provider.service.impl;

import cn.hutool.json.JSONUtil;
import com.asyf.provider.common.config.DataSourceKey;
import com.asyf.provider.common.config.DynamicDataSourceContextHolder;
import com.asyf.provider.service.JdbcService;
import io.seata.core.context.RootContext;
import io.seata.spring.annotation.GlobalTransactional;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.CallableStatementCallback;
import org.springframework.jdbc.core.JdbcOperations;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Service;

import java.sql.CallableStatement;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;

/**
 * @Author yjz
 * @Date 2021/7/6 17:42
 */
@Service
@Slf4j
public class JdbcServiceImpl implements JdbcService {

    @Autowired
    private NamedParameterJdbcTemplate jdbc;

    @Override
    public Object sqlTest(int a) {
//        String sql = "SELECT t.*,t.NAME as zhangsan FROM TEST t where t.name = '张三' and id = :id";
//        Map<String, Object> map = new HashMap<>();
//        map.put("id", a);
//        Map<String, Object> rs = jdbc.queryForMap(sql, map);
//        return rs;

        String sql = "call TEST_PROCEDURE2(?)";
//        jdbc.execute(sql, new PreparedStatementCallback<Object>() {
//            @Override
//            public Object doInPreparedStatement(PreparedStatement preparedStatement) throws SQLException, DataAccessException {
//                log.info("preparedStatement");
////                preparedStatement.setInt(1, 2);//这样传会报错
//                boolean execute = preparedStatement.execute();
//                log.info("执行结果:{}", execute);
//                return null;
//            }
//        });

        JdbcOperations jdbcOperations = jdbc.getJdbcOperations();

        jdbcOperations.execute(sql, new CallableStatementCallback() {
            @Override
            public Object doInCallableStatement(CallableStatement callableStatement) throws SQLException, DataAccessException {
                log.info("callableStatement");
                callableStatement.setInt(1, 2);
                boolean execute = callableStatement.execute();
                log.info("执行结果:{}", execute);
                return null;
            }
        });


        return null;
    }

    @GlobalTransactional
    @Override
    public Object transactionTest(int a) {
        String sql = "SELECT * FROM EMP where EMPNO = :empno";
        Map<String, Object> param = new HashMap<>();
//        param.put("empno", "7369");
//        Map<String, Object> map = jdbc.queryForMap(sql, param);
//        log.info(JSONUtil.toJsonStr(map));

        //插入
        sql = "insert into EMP (EMPNO) VALUES (:empno)";
        param.put("empno", 1111);
        jdbc.update(sql, param);
        int b = 1;
        b = b / a;

        return "";
    }

    @GlobalTransactional
    @Override
    public Object dynamicDataSourceTest(int a) {
        log.info("当前 XID: {}", RootContext.getXID());
        String sql = "SELECT * FROM EMP where EMPNO = :empno";
        Map<String, Object> param = new HashMap<>();
        param.put("empno", "7369");
        Map<String, Object> map = jdbc.queryForMap(sql, param);
        log.info(JSONUtil.toJsonStr(map));

        //切换数据源
        DynamicDataSourceContextHolder.setDataSourceKey(DataSourceKey.SECONDARY);
        log.info("当前 XID: {}", RootContext.getXID());

        //查询mysql数据库
        sql = "SELECT * FROM user where id = :id";
        param.put("id", "1");
        Map<String, Object> map2 = jdbc.queryForMap(sql, param);
        log.info(JSONUtil.toJsonStr(map2));
        map.putAll(map2);
        return map;
    }

    @GlobalTransactional
    @Override
    public Object dynamicDataSourceTxTest(int a) {
        Map<String, Object> param = new HashMap<>();
        //插入oracle
        insertOracle();

        int b = 1;
        b = b / a;

        //切换数据源
        DynamicDataSourceContextHolder.setDataSourceKey(DataSourceKey.SECONDARY);
        log.info("当前 XID: {}", RootContext.getXID());
        //插入mysql
        insertMysql();

        return null;
    }

    private void insertMysql() {
        String sql = "insert into user (id) VALUES (:id)";
        Map<String, Object> param = new HashMap<>();
        param.put("id", "1111");
        jdbc.update(sql, param);
    }

    private void insertOracle() {
        String sql = "insert into emp (EMPNO) VALUES (:empno)";
        Map<String, Object> param = new HashMap<>();
        param.put("empno", "1111");
        jdbc.update(sql, param);
    }

    @GlobalTransactional
    @Override
    public Object dynamicDataSourceTxCompTest(int a) {
        Map<String, Object> param = new HashMap<>();
        //插入oracle
        insertOracleComp();

        int b = 1;
        b = b / a;

        //切换数据源
        DynamicDataSourceContextHolder.setDataSourceKey(DataSourceKey.SECONDARY);
        log.info("当前 XID: {}", RootContext.getXID());
        //插入mysql
        insertMysql();

        return null;
    }

    private void insertOracleComp() {
        String sql = "insert into TEST (ID,NAME) VALUES (:id,'张三')";
        Map<String, Object> param = new HashMap<>();
        param.put("id", 1111);
        jdbc.update(sql, param);
    }
}
